RDBMS SOLUTION
ASSIGNMENT 5
SET B Q.1 SOLUTION
Write a cursor which will display Gym details having charges more than 5000 from ‘Pune’ city.
Source code:
/*Set b q 1 solution
Here first we will create tables and then insert the records and then we will create cursor*/
Create table gym(
gname varchar2(40) not null primary key,
city varchar2(40),
charges number,
scheme varchar2(40)
)
Create table member(
mid number not null primary key,
mname varchar2(40),
phone_no number,
address varchar2(40)
)
create table g_m(
gname varchar2(40) references gym(gname),
mid number references member(mid)
)
/*inserting records*/
insert into gym values('vijay','pune',6000,'none');
insert into gym values('stufnut','pune',7000,'none');
insert into gym values('blaster','pune',4000,'none');
insert into gym values('kim','pune',2000,'none');
insert into gym values('rocky','pune',8000,'none');
insert into gym values('jigar','mumbai',10000,'none');
insert into gym values('doms','mumbai',5000,'none');
insert into gym values('fit','mumbai',7000,'none');
insert into gym values('fitness','mumbai',3000,'none');
insert into member values( 1,'m1 ',37463928 ,'pune');
insert into member values( 2,' m2', 0837505,'pune');
insert into member values( 3,' m3',928509 ,'mumbai');
insert into member values( 4,' m4', 2845769,'mumbai');
insert into member values( 5,' m5',8092358 ,'mumbai');
insert into g_m values('vijay',1);
insert into g_m values('vijay',2);
insert into g_m values('stufnut',3);
insert into g_m values('stufnut',4);
insert into g_m values('blaster',5);
insert into g_m values('kim',6);
/*Cursor*/
Declare
v_gname gym.gname%type;
v_charges gym.charges%type;
cursor gym_det is
select gname,charges from gym
where city='pune' and charges > 5000;
begin
dbms_output.put_line('gym charges');
open gym_det;
loop
exit when gym_det%notfound;
fetch gym_det into v_gname,v_charges;
dbms_output.put_line(v_gname||' '||v_charges);
end loop;
close gym_det;
end;
/*source code link in description*/
/*Explaination:
The given code is a PL/SQL code block that fetches the name and charges of all gyms located in Pune with charges greater than 5000.
Let's break down the code:
1. Declaration of variables:
v_gname is a variable of type gym.gname%type, which is a user-defined data type based on the data type of the column gname in the table gym.
v_charges is a variable of type gym.charges%type, which is a user-defined data type based on the data type of the column charges in the table gym.
2. Cursor declaration:
The cursor gym_det is defined to select the gym name and charges from the gym table where the city is 'pune' and charges are greater than 5000.
3. The code block begins with the dbms_output.put_line statement which will display the heading 'gym charges' on the console.
4. The cursor gym_det is opened.
5. A loop is initiated using the loop keyword. The loop will continue until the gym_det%notfound condition is met, which indicates that all rows have been fetched from the cursor.
6. The fetch statement retrieves the next row from the cursor and assigns the values of the gname and charges columns to the variables v_gname and v_charges, respectively.
7. The dbms_output.put_line statement displays the values of v_gname and v_charges on the console separated by space.
8. The end loop statement ends the loop.
9. The close statement closes the cursor.
The final result of this code is to display the gym name and its charges, separated by space for all gyms located in Pune with charges greater than 5000 on the console. */
0 Comments